package managers;

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;

import structures.AD;
import structures.ADsList;
import structures.Product;
import structures.ProductList;
import structures.User;

public class DatabaseManager {
	private Connection connection = null;
	private static final String USERNAME = "root";
	private static final String PASSWORD = "";
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet;

	public void connect(){
        try {
        	//load the DB drivers
    		Class.forName("com.mysql.jdbc.Driver");
        	 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ServiceTechnologies",USERNAME, PASSWORD);
    	} catch (Exception e) {
    		System.out.println("Connection Failed! Check output console");
    		e.printStackTrace();
    	}
	}


	public User getUserByID(String idAsString, String social) {
		String query;
		User user;
		try{
			query = "select idUser, name, surname, email, city, cap, address, idFacebook, idGoogle, idTwitter, PayPalEmail from User where ";
			if(social.equals("TiVendoIo")){
				query+= "idUser = ?";
				preparedStatement = connection.prepareStatement(query);
				preparedStatement.setLong(1, Long.parseLong(idAsString));
			}else{
				switch(social){
				case "Facebook": 
					query+= "idFacebook = ?";
					break;
				case "Twitter": 
					query+= "idTwitter = ?";
					break;
				case "Google": 
					query+= "idGoogle = ?";
					break;
				}
				preparedStatement = connection.prepareStatement(query);
				preparedStatement.setString(1, idAsString);
			}
			resultSet = preparedStatement.executeQuery();
			//link all the resulting places to this SNPosts
			if(resultSet.next()){
				user = new User(resultSet.getInt("idUser"), resultSet.getString("Name"), resultSet.getString("Surname"), resultSet.getString("Email"), resultSet.getString("CAP"), resultSet.getString("City"), resultSet.getString("Address"), resultSet.getString("idGoogle"), resultSet.getString("idTwitter"), resultSet.getString("idFacebook"), resultSet.getString("PayPalEmail"));
				return user;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	public void closeConnection() {
		try {
	           if (preparedStatement != null) {
	        	   preparedStatement.close();
	           }
	           if (connection != null) {
	               connection.close();
	           }
		} catch (Exception ex) {
	           System.err.println(ex);
        }
	}


	public User insertUser(String name, String surname, String email, String password, String cap, String city, String address, String payPalEmail) {
		ResultSet resultSet;
		User user;
		try {
			preparedStatement = connection.prepareStatement("insert into User (Name,Surname,Email,Password,CAP,City,Address,PayPalEmail) values ( ?, ?, ?, ?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, surname);
			preparedStatement.setString(3, email);
			preparedStatement.setString(4, password);
			preparedStatement.setString(5, cap);
			preparedStatement.setString(6, city);
			preparedStatement.setString(7, address);
			preparedStatement.setString(8, payPalEmail);
			preparedStatement.executeUpdate();
			resultSet = preparedStatement.getGeneratedKeys();
			if(resultSet.next()){
				if(resultSet.getInt(1)==0)
					throw new Exception();
				user = new User(resultSet.getInt(1), name, surname, email, cap, city, address, null, null, null, payPalEmail);
				return user;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	public void updateUser(String idUser, String social, String idSocial) throws SQLException {
		String query;
		query = "UPDATE User SET ";
		switch(social){
		case "Facebook": 
			query+= "idFacebook = ? ";
			break;
		case "Twitter": 
			query+= "idTwitter = ? ";
			break;
		case "Google": 
			query+= "idGoogle = ? ";
			break;
		}
		query += "WHERE idUser = ?";
		preparedStatement = connection.prepareStatement(query);
		preparedStatement.setString(1, idSocial);
		preparedStatement.setInt(2, Integer.parseInt(idUser));
		preparedStatement.executeUpdate();
	}

	public void activateUser(String idUser) throws SQLException {
		String query;
		query = "UPDATE User SET Active = ? WHERE idUser = ?";
		preparedStatement = connection.prepareStatement(query);
		preparedStatement.setBoolean(1, true);
		preparedStatement.setInt(2, Integer.parseInt(idUser));
		preparedStatement.executeUpdate();
	}
	

	public User getUserByCredentials(String email, String password) {
		String query;
		User user;
		try{
			query = "select idUser, name, surname, email, city, cap, address, idFacebook, idGoogle, idTwitter, PayPalEmail from User where email = ? and password = ?";
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, email);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			//link all the resulting places to this SNPosts
			if(resultSet.next()){
				user = new User(resultSet.getInt("idUser"), resultSet.getString("Name"), resultSet.getString("Surname"), resultSet.getString("Email"), resultSet.getString("CAP"), resultSet.getString("City"), resultSet.getString("Address"), resultSet.getString("idGoogle"), resultSet.getString("idTwitter"), resultSet.getString("idFacebook"), resultSet.getString("PayPalEmail"));
				return user;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public AD insertAD(String description, InputStream photo, String title, String priceAsString, String idUser, String payPalEmail){
		try {
			String query;
			double price;
			AD advertisement;
            // constructs SQL statement
			price= Double.parseDouble(priceAsString);
            query = "INSERT INTO Advertisement (Description, Title, Price, Photo, idUser, PayPalEmail) values (?, ?, ?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, description);
            preparedStatement.setString(2, title);
            preparedStatement.setDouble(3, price);
            preparedStatement.setLong(5, Long.parseLong(idUser));
            preparedStatement.setString(6, payPalEmail);
            if (photo != null) {
                // fetches input stream of the upload file for the blob column
            	preparedStatement.setBlob(4, photo);
            }
 
            // sends the statement to the database server
            preparedStatement.executeUpdate();
			resultSet = preparedStatement.getGeneratedKeys();
			if(resultSet.next()){
				if(resultSet.getInt(1)==0)
					throw new Exception();
				advertisement = new AD(resultSet.getInt(1), description, title, price, Long.parseLong(idUser), payPalEmail, null, null, 0);
				return advertisement;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	public Blob getImageByIdAdvertisement(String idAdvertisement) {
		try {
			preparedStatement = connection.prepareStatement("select Photo from Advertisement where idAdvertisement = ?");
			preparedStatement.setLong(1,Long.parseLong(idAdvertisement));
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				return resultSet.getBlob("Photo");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	public AD getADByIdAdvertisement(String idAdvertisementAsString) {
		try {
			String query;
			long idAdvertisement;
			AD advertisement;
			idAdvertisement = Long.parseLong(idAdvertisementAsString);
            // constructs SQL statement
            query = "SELECT Description, Title, Price, idUser, PayPalEmail, BoughtBy, FeedbackText, FeedbackValue FROM Advertisement WHERE idAdvertisement= ?";
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setLong(1, idAdvertisement);
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				advertisement = new AD(idAdvertisement, resultSet.getString("Description"), resultSet.getString("Title"), resultSet.getDouble("Price"), resultSet.getLong("idUser"), resultSet.getString("PayPalEmail"), resultSet.getString("BoughtBy"), resultSet.getString("FeedbackText"), resultSet.getInt("FeedbackValue"));
				return advertisement;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	public User modifyUser(String name, String surname, String cap, String city, String address, String payPalEmail, String idUser) {
		String query;
		query = "UPDATE User SET Name = ? , Surname = ? , CAP = ? , City = ? , Address = ? , PayPalEmail = ? ";
		query += "WHERE idUser = ?";
		try {
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, surname);
			preparedStatement.setString(3, cap);
			preparedStatement.setString(4, city);
			preparedStatement.setString(5, address);
			preparedStatement.setString(6, payPalEmail);
			preparedStatement.setLong(7, Long.parseLong(idUser));
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return getUserByID(idUser);
	}


	public User getUserByID(String idUser) {
		String query;
		User user;
		try{
			query = "select idUser, name, surname, email, city, cap, address, idFacebook, idGoogle, idTwitter, PayPalEmail from User where idUser = ?";
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, Long.parseLong(idUser));
			resultSet = preparedStatement.executeQuery();
			//link all the resulting places to this SNPosts
			if(resultSet.next()){
				user = new User(resultSet.getInt("idUser"), resultSet.getString("Name"), resultSet.getString("Surname"), resultSet.getString("Email"), resultSet.getString("CAP"), resultSet.getString("City"), resultSet.getString("Address"), resultSet.getString("idGoogle"), resultSet.getString("idTwitter"), resultSet.getString("idFacebook"), resultSet.getString("PayPalEmail"));
				return user;
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}


	public ADsList getADs(String limitAsString, String starterIdAsString, String method, String adType) {
		String query;
		long starterId, id, limit;
		ADsList adsList;
		LinkedList<AD> temp;
		AD advertisement;
		adsList = new ADsList();
		try {
			starterId = Long.parseLong(starterIdAsString);
			limit = Long.parseLong(limitAsString);
            // constructs SQL statement
			query = "SELECT idAdvertisement, Description, Title, Price, idUser, PayPalEmail, BoughtBy, FeedbackText, FeedbackValue FROM Advertisement WHERE idAdvertisement ";
			if(method.equals("successors"))
				query += " > ? ";
			else
				query += " < ? ";
			switch(adType){
			case "Bought":
				query += " AND BoughtBy is not null ";
				break;
			case "Available":
				query += " AND BoughtBy is null ";
				break;
			case "All":
				break;
			}
			query += " ORDER BY idAdvertisement LIMIT ?";
			preparedStatement = connection.prepareStatement(query);
            preparedStatement.setLong(1, starterId);
            preparedStatement.setLong(2, limit);
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				advertisement = new AD(resultSet.getLong("idAdvertisement"), resultSet.getString("Description"), resultSet.getString("Title"), resultSet.getDouble("Price"), resultSet.getLong("idUser"), resultSet.getString("PayPalEmail"), resultSet.getString("BoughtBy"), resultSet.getString("FeedbackText"), resultSet.getInt("FeedbackValue"));
				adsList.addAD(advertisement);
			}
			if(adsList.getAdsList().size()==0)
				return adsList;
			
			if(!method.equals("successors")){
				//sort the advertisement
				temp = new LinkedList<AD>();
				for(AD ad: adsList.getAdsList()){
					temp.addFirst(ad);
				}
				adsList.setAdsList(temp);
			}
			//check if there are predecessors
			query = "SELECT idAdvertisement FROM Advertisement WHERE idAdvertisement < ? ";
			switch(adType){
			case "Bought":
				query += " AND BoughtBy is not null ";
				break;
			case "Available":
				query += " AND BoughtBy is null ";
				break;
			case "All":
				break;
			}
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setLong(1, adsList.getMinId());
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				id = resultSet.getLong("idAdvertisement");
				if(id!=0)
					adsList.setHasPredecessors(true);
				else
					adsList.setHasPredecessors(false);
			}else
				adsList.setHasPredecessors(false);
			
			//check if there are successors
			query = "SELECT idAdvertisement FROM Advertisement WHERE idAdvertisement > ?  ";
			switch(adType){
			case "Bought":
				query += " AND BoughtBy is not null ";
				break;
			case "Available":
				query += " AND BoughtBy is null ";
				break;
			case "All":
				break;
			}
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setLong(1, adsList.getMaxId());
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				id = resultSet.getLong("idAdvertisement");
				if(id!=0)
					adsList.setHasSuccessors(true);
				else
					adsList.setHasSuccessors(false);
			}else
				adsList.setHasSuccessors(false);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return adsList;
	}


	public ADsList searchADsByTitle(String limitAsString, String starterIdAsString, String method, String title, String adType) {
		String query;
		long starterId, id, limit;
		ADsList adsList;
		LinkedList<AD> temp;
		AD advertisement;
		adsList = new ADsList();
		try {
			starterId = Long.parseLong(starterIdAsString);
			limit = Long.parseLong(limitAsString);
            // constructs SQL statement
			query = "SELECT idAdvertisement, Description, Title, Price, idUser, PayPalEmail, BoughtBy, FeedbackText, FeedbackValue FROM Advertisement WHERE idAdvertisement ";
			if(method.equals("successors"))
				query += " > ? AND Title = ? ";
			else
				query += " < ? AND Title = ? ";
			switch(adType){
			case "Bought":
				query += " AND BoughtBy is not null ";
				break;
			case "Available":
				query += " AND BoughtBy is null ";
				break;
			case "All":
				break;
			}
			query += " ORDER BY idAdvertisement LIMIT ?";
			preparedStatement = connection.prepareStatement(query);
            preparedStatement.setLong(1, starterId);
            preparedStatement.setString(2, title);
            preparedStatement.setLong(3, limit);
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				advertisement = new AD(resultSet.getLong("idAdvertisement"), resultSet.getString("Description"), resultSet.getString("Title"), resultSet.getDouble("Price"), resultSet.getLong("idUser"), resultSet.getString("PayPalEmail"), resultSet.getString("BoughtBy"), resultSet.getString("FeedbackText"), resultSet.getInt("FeedbackValue"));
				adsList.addAD(advertisement);
			}
			if(adsList.getAdsList().size()==0)
				return adsList;
			
			if(!method.equals("successors")){
				//sort the advertisement
				temp = new LinkedList<AD>();
				for(AD ad: adsList.getAdsList()){
					temp.addFirst(ad);
				}
				adsList.setAdsList(temp);
			}
			//check if there are predecessors
			query = "SELECT idAdvertisement FROM Advertisement WHERE idAdvertisement < ? AND Title = ? ";
			switch(adType){
			case "Bought":
				query += " AND BoughtBy is not null ";
				break;
			case "Available":
				query += " AND BoughtBy is null ";
				break;
			case "All":
				break;
			}
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setLong(1, adsList.getMinId());
            preparedStatement.setString(2, title);
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				id = resultSet.getLong("idAdvertisement");
				if(id!=0)
					adsList.setHasPredecessors(true);
				else
					adsList.setHasPredecessors(false);
			}else
				adsList.setHasPredecessors(false);
			
			//check if there are successors
			query = "SELECT idAdvertisement FROM Advertisement WHERE idAdvertisement > ? AND Title = ? ";
			switch(adType){
			case "Bought":
				query += " AND BoughtBy is not null ";
				break;
			case "Available":
				query += " > ? AND BoughtBy is null ";
				break;
			case "All":
				break;
			}
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setLong(1, adsList.getMaxId());
            preparedStatement.setString(2, title);
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				id = resultSet.getLong("idAdvertisement");
				if(id!=0)
					adsList.setHasSuccessors(true);
				else
					adsList.setHasSuccessors(false);
			}else
				adsList.setHasSuccessors(false);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return adsList;
	}


	public boolean SetADBought(String idAdvertisement, String idUser) {
		String query;
		query = "UPDATE Advertisement SET BoughtBy = ? WHERE idAdvertisement = ?";
		try {
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, idUser);
			preparedStatement.setString(2, idAdvertisement);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return true;
		}
		return false;
	}


	public AD SetFeedback(String idAdvertisement, String feedbackText, String feedbackValue) {
		String query;
		query = "UPDATE Advertisement SET FeedbackText = ?, FeedbackValue = ? WHERE idAdvertisement = ?";
		try {
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, feedbackText);
			preparedStatement.setBoolean(2, Boolean.parseBoolean(feedbackValue));
			preparedStatement.setLong(3, Long.parseLong(idAdvertisement));
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return getADByIdAdvertisement(idAdvertisement);
	}


	public ADsList getADs(String idSeller, String adType) {
		String query;
		ADsList adsList;
		AD advertisement;
		adsList = new ADsList();
		try {
			query = "SELECT idAdvertisement, Description, Title, Price, idUser, PayPalEmail, BoughtBy, FeedbackText, FeedbackValue FROM Advertisement WHERE ";
            // constructs SQL statement
			switch(adType){
			case "Sold":
				query += " BoughtBy is not null AND idUser = ? ";
				break;
			case "Available":
				query += " BoughtBy is null AND idUser = ? ";
				break;
			case "Bought":
				query += " BoughtBy = ? ";
				break;
			case "All":
				break;
			}
			query += " ORDER BY idAdvertisement ";
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, Long.parseLong(idSeller));
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				advertisement = new AD(resultSet.getLong("idAdvertisement"), resultSet.getString("Description"), resultSet.getString("Title"), resultSet.getDouble("Price"), resultSet.getLong("idUser"), resultSet.getString("PayPalEmail"), resultSet.getString("BoughtBy"), resultSet.getString("FeedbackText"), resultSet.getInt("FeedbackValue"));
				adsList.addAD(advertisement);
			}
		}catch(Exception e){
			return null;
		}
		return adsList;
	}


	public ProductList getProductListByKeyword(String keywords) {
		Product product;
		ArrayList<Product> list;
		String query, idAdvertisement;
		list = new ArrayList<>();
		try {
            // constructs SQL statement
            query = "SELECT idAdvertisement, Title FROM Advertisement WHERE Title like ?";
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, "%"+keywords+"%");
            // sends the statement to the database server
            resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				idAdvertisement = Long.toString(resultSet.getLong("idAdvertisement"));
				product = new Product(idAdvertisement, resultSet.getString("Title"), "", "", "iselltoyou");
				list.add(product);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new ProductList(list);
	}
}
